package sc.test.spring.tool.sqlgen;

import java.io.BufferedReader;
import java.io.FileWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.sql.rowset.serial.SerialClob;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import sc.common.test.TestUtil;
import sc.test.spring.jdbc.JdbcUtil;

/**
 * 用于生成Sql Insert语句
 *
 * @author Neo
 */
public class SqlGenTool {

    public static class TableInput {
        public TableInput(String dburl, String username, String password, String tableName, String selectSql,
                String outputFileName, String resetNumColumn, int resetNumInit, String[] columnsTosysdate,
                String outputFolder, HashMap<String, HashMap<String, String>> columnReplace) {
            this.dburl = dburl;
            this.username = username;
            this.password = password;
            this.tableName = tableName;
            this.selectSql = selectSql;
            this.outputFileName = outputFileName;
            if (StringUtils.isNotBlank(resetNumColumn)) {
                this.resetNumColumn = resetNumColumn;
            } else {
                this.resetNumColumn = "";
            }
            this.resetNumInit = resetNumInit;
            if (columnsTosysdate != null) {
                this.columnsTosysdate = Arrays.asList(columnsTosysdate);
            } else {
                this.columnsTosysdate = new ArrayList<String>();
            }
            if (StringUtils.isNotBlank(outputFolder)) {
                this.outputFolder = outputFolder;
            }
            if (columnReplace != null) {
                this.columnReplace = columnReplace;
            }
        }

        /** 数据库连接串,用户名,密码 */
        public String dburl, username, password;
        /** 需要生成的表名，当selectSql为空时，自动构建select * from tableName查询需要导出的数据 */
        public String tableName;
        /** 查询Sql，不可空时，使用此Sql查询需要导出的数据*/
        public String selectSql;
        /** 转化成sysdate的列名*/
        public List<String> columnsTosysdate;
        /** 重新开始标号的列名，可为空*/
        public String resetNumColumn = null;
        /** 重新开始标号的起始值*/
        public int resetNumInit = 1;
        /** 列内容替换 <列名,<原始Regex，替换内容>>*/
        public HashMap<String, HashMap<String, String>> columnReplace = new HashMap<String, HashMap<String, String>>();
        /** 输出文件夹，不可为空*/
        public String outputFolder = "D:/Temp/";
        /** 输出文件名，为空时自动根据tableName拼接*/
        public String outputFileName;
    }

    public List<TableInput> myTest() {
        String dburl = "jdbc:oracle:thin:@localhost:1521:XE", username = "mytest", password = "mytest";
        return Arrays.asList(new TableInput(dburl, username, password, "t_config", null, null, null, 0, null, null,
                null));
    }

    public List<TableInput> pfs() {
        String dburl = "jdbc:oracle:thin:@192.168.100.186:1521:qijia", username = "reader", password = "reader";
        String outputFolder = "D:/QijiaWorkspace/db_export/";
        String[] toSysdate = Arrays.asList("GMT_CREATE", "GMT_MODIFY").toArray(new String[0]);
        List<TableInput> ts = new ArrayList<TableInput>();

        HashMap<String, HashMap<String, String>> columnMap1 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMap11 = new HashMap<String, String>();
        columnMap11.put("\\d+", "1");
        columnMap1.put("VERSION", columnMap11);
        ts.add(new TableInput(dburl, username, password, "pfs.tb_bank_info",
                "select * from pfs.tb_bank_info order by unified_bank_no", "tb_bank_info.sql", "", 0, toSysdate,
                outputFolder, columnMap1));

        HashMap<String, HashMap<String, String>> columnMap2 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMap21 = new HashMap<String, String>();
        columnMap21.put("\\d+", "pfs.seq_card_bin.nextval");
        columnMap2.put("BIN_ID", columnMap21);
        HashMap<String, String> columnMap22 = new HashMap<String, String>();
        columnMap22.put("\\d+", "1");
        columnMap2.put("VERSION", columnMap22);
        ts.add(new TableInput(
                dburl,
                username,
                password,
                "pfs.tb_card_bin",
                "select * from pfs.tb_card_bin where enable_flag = 'Y' order by bank_code, card_type, card_length, bin_no",
                "tb_card_bin.sql", "", 0, toSysdate, outputFolder, columnMap2));

        HashMap<String, HashMap<String, String>> columnMap3 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMap31 = new HashMap<String, String>();
        columnMap31.put("\\d+", "1");
        columnMap3.put("VERSION", columnMap31);
        ts.add(new TableInput(dburl, username, password, "pfs.tb_prov_info",
                "select * from pfs.tb_prov_info where enable_flag = 'Y' order by prov_id", "tb_prov_info.sql", "", 0,
                toSysdate, outputFolder, columnMap3));

        HashMap<String, HashMap<String, String>> columnMap4 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMap41 = new HashMap<String, String>();
        columnMap41.put("\\d+", "1");
        columnMap4.put("VERSION", columnMap41);
        ts.add(new TableInput(dburl, username, password, "pfs.tb_city_info",
                "select * from pfs.tb_city_info where enable_flag = 'Y' order by city_id", "tb_city_info.sql", "", 0,
                toSysdate, outputFolder, columnMap4));

        HashMap<String, HashMap<String, String>> columnMap5 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMap51 = new HashMap<String, String>();
        columnMap51.put("\\d+", "1");
        columnMap5.put("VERSION", columnMap51);
        ts.add(new TableInput(dburl, username, password, "pfs.tb_area_info",
                "select * from pfs.tb_area_info where enable_flag = 'Y' order by area_code", "tb_area_info.sql", "", 0,
                toSysdate, outputFolder, columnMap5));

        HashMap<String, HashMap<String, String>> columnMapT1 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMapT11 = new HashMap<String, String>();
        columnMapT11.put("\\d+", "pfs.seq_card_bin.nextval");
        columnMapT1.put("BIN_ID", columnMapT11);
        HashMap<String, String> columnMapT12 = new HashMap<String, String>();
        columnMapT12.put("\\d+", "1");
        columnMapT1.put("VERSION", columnMapT12);
        ts.add(new TableInput(
                dburl,
                username,
                password,
                "pfs.tb_card_bin",
                "select * from pfs.tb_card_bin where bank_code in ('SCB', 'CYB') and enable_flag = 'Y' order by bank_code, card_type, card_length, bin_no",
                "tb_card_bin_temp.sql", "", 0, toSysdate, outputFolder, columnMapT1));

        HashMap<String, HashMap<String, String>> columnMapT2 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMapT21 = new HashMap<String, String>();
        columnMapT21.put("\\d+", "1");
        columnMapT2.put("VERSION", columnMapT21);
        ts.add(new TableInput(dburl, username, password, "pfs.tb_bank_info",
                "select * from pfs.tb_bank_info order by unified_bank_no", "tb_bank_info.sql", "", 0, toSysdate,
                outputFolder, columnMapT2));

        return ts;
    }

    public List<TableInput> pfs_temp() {
        String dburl = "jdbc:oracle:thin:@192.168.100.186:1521:qijia", username = "reader", password = "reader";
        String outputFolder = "D:/QijiaWorkspace/db_export/";
        String[] toSysdate = Arrays.asList("GMT_CREATE", "GMT_MODIFY").toArray(new String[0]);
        List<TableInput> ts = new ArrayList<TableInput>();

        HashMap<String, HashMap<String, String>> columnMapT3 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMapT31 = new HashMap<String, String>();
        columnMapT31.put("\\d+", "pfs.seq_branch_info.nextval");
        columnMapT3.put("BRANCH_ID", columnMapT31);
        HashMap<String, String> columnMapT32 = new HashMap<String, String>();
        columnMapT32.put("\\d+", "1");
        columnMapT3.put("VERSION", columnMapT32);
        ts.add(new TableInput(
                dburl,
                username,
                password,
                "pfs.tb_branch_info",
                "select * from pfs.tb_branch_info where bank_code in ('SCB', 'CYB') and enable_flag = 'Y' order by branch_no",
                "tb_branch_info_temp.sql", "", 0, toSysdate, outputFolder, columnMapT3));

        return ts;
    }

    public List<TableInput> pfs_branch() {
        String dburl = "jdbc:oracle:thin:@192.168.100.186:1521:qijia", username = "reader", password = "reader";
        String outputFolder = "D:/QijiaWorkspace/db_export/";
        String[] toSysdate = Arrays.asList("GMT_CREATE", "GMT_MODIFY").toArray(new String[0]);
        List<TableInput> ts = new ArrayList<TableInput>();

        HashMap<String, HashMap<String, String>> columnMap1 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMap11 = new HashMap<String, String>();
        columnMap11.put("\\d+", "pfs.seq_branch_info.nextval");
        columnMap1.put("BRANCH_ID", columnMap11);
        HashMap<String, String> columnMap12 = new HashMap<String, String>();
        columnMap12.put("\\d+", "1");
        columnMap1.put("VERSION", columnMap12);
        ts.add(new TableInput(dburl, username, password, "pfs.tb_branch_info",
                "select * from pfs.tb_branch_info where enable_flag = 'Y' order by branch_no", "tb_branch_info.sql",
                "", 0, toSysdate, outputFolder, columnMap1));

        return ts;
    }

    public List<TableInput> pfs_payment() {
        String dburl = "jdbc:oracle:thin:@192.168.100.186:1521:qijia", username = "reader", password = "reader";
        String outputFolder = "D:/QijiaWorkspace/db_export/";
        String[] toSysdate = Arrays.asList("GMT_CREATE", "GMT_MODIFY").toArray(new String[0]);
        List<TableInput> ts = new ArrayList<TableInput>();

        ts.add(new TableInput(dburl, username, password, "payment.tb_biz_product_map",
                "select * from payment.tb_biz_product_map where enable_flag = 'Y' order by biz_product_code",
                "tb_biz_product_map.sql", "", 0, toSysdate, outputFolder, null));

        HashMap<String, HashMap<String, String>> columnMapGM = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMapGMInner = new HashMap<String, String>();
        columnMapGMInner.put("\\d+", "payment.seq_generate_map.nextval");
        columnMapGM.put("MAP_ID", columnMapGMInner);
        ts.add(new TableInput(dburl, username, password, "payment.tb_generate_map",
                "select * from payment.tb_generate_map where enable_flag = 'Y' order by map_code",
                "tb_generate_map.sql", "", 0, toSysdate, outputFolder, columnMapGM));

        ts.add(new TableInput(dburl, username, password, "payment.tb_generate_group",
                "select * from payment.tb_generate_group where enable_flag = 'Y' order by gen_group_code",
                "tb_generate_group.sql", "", 0, toSysdate, outputFolder, null));

        HashMap<String, HashMap<String, String>> columnMapG = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMapGInner = new HashMap<String, String>();
        columnMapGInner.put("\\d+", "payment.seq_generate.nextval");
        columnMapG.put("GEN_ID", columnMapGInner);
        ts.add(new TableInput(dburl, username, password, "payment.tb_generate",
                "select * from payment.tb_generate where enable_flag = 'Y' order by gen_group_code, gen_priority",
                "tb_generate.sql", "", 0, toSysdate, outputFolder, columnMapG));

        ts.add(new TableInput(dburl, username, password, "payment.tb_message_eventcode",
                "select * from payment.tb_message_eventcode where enable_flag = 'Y' order by event_code_id",
                "tb_message_eventcode.sql", "", 0, toSysdate, outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "payment.tb_message_config",
                "select * from payment.tb_message_config where enable_flag = 'Y' order by config_id",
                "tb_message_config.sql", "", 0, toSysdate, outputFolder, null));

        return ts;
    }

    public List<TableInput> basis() {
        String dburl = "jdbc:oracle:thin:@192.168.100.186:1521:qijia", username = "reader", password = "reader";
        String outputFolder = "D:/QijiaWorkspace/db_export/";
        String[] toSysdate = Arrays.asList("GMT_CREATE", "GMT_MODIFIED").toArray(new String[0]);
        List<TableInput> ts = new ArrayList<TableInput>();

        ts.add(new TableInput(dburl, username, password, "basis.tb_uq_define",
                "select * from basis.tb_uq_define where status= 'N' order by priority_level", "tb_uq_define.sql", "",
                0, toSysdate, outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "basis.tb_uq_data_source",
                "select * from basis.tb_uq_data_source order by source_id", "tb_uq_data_source.sql", "", 0, toSysdate,
                outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "basis.tb_uq_order_type",
                "select * from basis.tb_uq_order_type order by code", "tb_uq_order_type.sql", "", 0, toSysdate,
                outputFolder, null));

        ts.add(new TableInput(
                dburl,
                username,
                password,
                "basis.tb_uq_param",
                "select p.* from basis.tb_uq_param p, basis.tb_uq_define d where p.define_id = d.define_id and d.status = 'N' order by p.define_id, p.field_name",
                "tb_uq_param.sql", "", 0, toSysdate, outputFolder, null));

        return ts;
    }

    public List<TableInput> rms() {
        String dburl = "jdbc:oracle:thin:@oracle.dev.netfinworks.com:1521:fujiedev", username = "reader", password = "reader";
        String outputFolder = "D:/QijiaWorkspace/db_export/";
        String[] toSysdate = Arrays.asList("CREATED_AT", "UPDATED_AT").toArray(new String[0]);
        List<TableInput> ts = new ArrayList<TableInput>();

        ts.add(new TableInput(dburl, username, password, "rms.sars_check_point",
                "select * from rms.sars_check_point order by code", "sars_check_point.sql", "", 0, toSysdate,
                outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "rms.sars_event_field",
                "select * from rms.sars_event_field order by check_point, id", "sars_event_field.sql", "", 0,
                toSysdate, outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "rms.sars_event_store_cfg",
                "select * from rms.sars_event_store_cfg order by check_point", "sars_event_store_cfg.sql", "", 0,
                toSysdate, outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "rms.sars_filter_field",
                "select * from rms.sars_filter_field order by id", "sars_filter_field.sql", "", 0, toSysdate,
                outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "rms.sars_policy",
                "select * from rms.sars_policy order by check_point", "sars_policy.sql", "", 0, toSysdate,
                outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "rms.tb_rule_parameter",
                "select * from rms.tb_rule_parameter order by code", "tb_rule_parameter.sql", "", 0, toSysdate,
                outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "rms.tb_rule_parameter_value",
                "select * from rms.tb_rule_parameter_value order by param_id", "tb_rule_parameter_value.sql", "", 0,
                toSysdate, outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "rms.sars_rule", "select * from rms.sars_rule",
                "sars_rule.sql", "", 0, toSysdate, outputFolder, null));

        return ts;
    }

    public List<TableInput> csa_new() {
        String dburl = "jdbc:oracle:thin:@oracle.dev.netfinworks.com:1521:fujiedev", username = "reader", password = "reader";
        String outputFolder = "D:/QijiaWorkspace/db_export/csa/";
        String[] toSysdate = Arrays.asList("GMT_CREATE", "GMT_MODIFY").toArray(new String[0]);
        List<TableInput> ts = new ArrayList<TableInput>();

        ts.add(new TableInput(dburl, username, password, "csa.t_csa_label",
                "select * from csa.t_csa_label order by label_id", "t_csa_label.sql", "", 0, toSysdate, outputFolder,
                null));

        ts.add(new TableInput(dburl, username, password, "csa.t_config",
                "select * from csa.t_config order by config_id", "t_config.sql", "", 0, toSysdate, outputFolder, null));

        ts.add(new TableInput(dburl, username, password, "csa.t_definition",
                "select * from csa.t_definition where isvalid = 'Y' order by def_id", "t_definition.sql", "", 0,
                toSysdate, outputFolder, null));

        ts.add(new TableInput(
                dburl,
                username,
                password,
                "csa.t_definition_ext",
                "select de.* from csa.t_definition_ext de, csa.t_definition d where de.def_id = d.def_id and d.isvalid = 'Y' order by de.ext_id",
                "t_definition_ext.sql", "", 0, toSysdate, outputFolder, null));

        ts.add(new TableInput(
                dburl,
                username,
                password,
                "csa.t_definition_rule",
                "select dr.* from csa.t_definition_rule dr, csa.t_definition d where dr.def_id = d.def_id and d.isvalid = 'Y' order by dr.rule_id",
                "t_definition_rule.sql", "", 0, toSysdate, outputFolder, null));

        HashMap<String, HashMap<String, String>> columnMap1 = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> columnMap11 = new HashMap<String, String>();
        columnMap11.put("\\d+", "0");
        columnMap1.put("LAST_TASK_ID", columnMap11);
        ts.add(new TableInput(
                dburl,
                username,
                password,
                "csa.t_schedule",
                "select s.* from csa.t_schedule s, csa.t_definition d where s.def_id = d.def_id and d.isvalid = 'Y' and s.isvalid = 'Y' order by s.sched_id",
                "t_schedule.sql", "", 0, toSysdate, outputFolder, columnMap1));

        ts.add(new TableInput(
                dburl,
                username,
                password,
                "csa.t_schedule_ext",
                "select se.* from csa.t_schedule_ext se, csa.t_schedule s, csa.t_definition d where se.sched_id = s.sched_id and s.def_id = d.def_id and s.isvalid = 'Y' and d.isvalid = 'Y' order by se.ext_id",
                "t_schedule_ext.sql", "", 0, toSysdate, outputFolder, null));

        return ts;
    }

    public List<TableInput> paymentMessageEventCode() {
        String dburl = "jdbc:oracle:thin:@10.65.193.12:1521:whpay", username = "reader", password = "reader";
        String outputFolder = "D:/WeibopayTest/PAYMENT/ENV_DEV/";
        String[] toSysdate = Arrays.asList("GMT_CREATE", "GMT_MODIFY").toArray(new String[0]);
        List<TableInput> ts = new ArrayList<TableInput>();
        String sql1 = "select * from payment.tb_message_eventcode where event_code_id in (100101, 100102, 100103)";
        ts.add(new TableInput(dburl, username, password, "payment.tb_message_eventcode", sql1, "message_eventcode.sql",
                "", 0, toSysdate, outputFolder, null));
        String sql2 = "select * from payment.tb_message_config where config_id in (10010101, 10010201, 10010301)";
        ts.add(new TableInput(dburl, username, password, "payment.tb_message_config", sql2, "message_config.sql", "",
                0, toSysdate, outputFolder, null));
        return ts;
    }

    public List<TableInput> csa() {
        String dburl = "jdbc:oracle:thin:@10.65.193.12:1521:whpay", username = "reader", password = "reader";
        String outputFolder = "D:/WeibopayTest/CSA/ENV_TEST/";
        String[] toSysdate = Arrays.asList("GMT_CREATE", "GMT_MODIFY").toArray(new String[0]);
        List<TableInput> ts = new ArrayList<TableInput>();
        //        String defSql = "select * from csa.t_definition where def_id = 112 order by def_id";
        //        String defExtSql = "select * from csa.t_definition_ext where ext_id >= 100 and def_id = 112";
        //        String defRuleSql = "select * from csa.t_definition_rule where rule_id >= 100 and def_id = 112";
        String defSql = "select * from csa.t_definition d where d.def_id >= 121 and d.def_id <= 128 order by d.def_id";
        String defExtSql = "select * from csa.t_definition_ext de where de.def_id >= 121 and de.def_id <= 128 order by de.def_id, de.ext_id";
        String defRuleSql = "select * from csa.t_definition_rule dr where dr.def_id >= 121 and dr.def_id <= 128 order by dr.def_id, dr.rule_id";
        //String schedSql = "select * from csa.t_schedule where sched_id = 1000 order by sched_id";
        //String schedExtSql = "select * from csa.t_schedule_ext where sched_id = 1000 order by sched_id, ext_id";
        //String taskSql = "select * from csa.t_task where task_id < 1000 order by task_id";
        //String taskExtSql = "select * from csa.t_task_ext where task_id < 1000 order by task_id, ext_id";
        int defReset = 1;
        int defExtIdReset = 1;
        int defRuleIdReset = 1;
        //        int schedIdReset = 1;
        //        int schedExtIdReset = 1;
        //        int taskIdReset = 1;
        //        int taskExtIdReset = 1;

        HashMap<String, HashMap<String, String>> dsReplaces = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> dsReplace = new HashMap<String, String>();
        dsReplaces.put("SRC_DS_ID", dsReplace);
        dsReplaces.put("TAR_DS_ID", dsReplace);
        dsReplace.put("382", "362");
        ts.add(new TableInput(dburl, username, password, "csa.t_definition", defSql, "definition.sql", "DEF_ID",
                defReset, toSysdate, outputFolder, dsReplaces));

        HashMap<String, HashMap<String, String>> defIdReplaces = new HashMap<String, HashMap<String, String>>();
        HashMap<String, String> defIdReplace = new HashMap<String, String>();
        defIdReplaces.put("DEF_ID", defIdReplace);
        defIdReplace.put("^121$", "1");
        defIdReplace.put("^122$", "2");
        defIdReplace.put("^123$", "3");
        defIdReplace.put("^124$", "4");
        defIdReplace.put("^125$", "5");
        defIdReplace.put("^126$", "6");
        defIdReplace.put("^127$", "7");
        defIdReplace.put("^128$", "8");
        ts.add(new TableInput(dburl, username, password, "csa.t_definition_ext", defExtSql, "definition_ext.sql",
                "EXT_ID", defExtIdReset, toSysdate, outputFolder, defIdReplaces));
        ts.add(new TableInput(dburl, username, password, "csa.t_definition_rule", defRuleSql, "definition_rule.sql",
                "RULE_ID", defRuleIdReset, toSysdate, outputFolder, defIdReplaces));

        //        HashMap<String, HashMap<String, String>> schedReplaces = new HashMap<String, HashMap<String, String>>();
        //        HashMap<String, String> schedIdReplace = new HashMap<String, String>();
        //        schedReplaces.put("SCHED_ID", schedIdReplace);
        //        schedIdReplace.put("^1000$", "1");
        //        ts.add(new TableInput(dburl, username, password, "csa.t_schedule", schedSql, "schedule.new.sql", "SCHED_ID",
        //                schedIdReset, toSysdate, outputFolder, null));
        //        ts.add(new TableInput(dburl, username, password, "csa.t_schedule_ext", schedExtSql, "schedule_ext.new.sql",
        //                "EXT_ID", schedExtIdReset, toSysdate, outputFolder, schedReplaces));

        //        HashMap<String, HashMap<String, String>> taskReplaces = new HashMap<String, HashMap<String, String>>();
        //        HashMap<String, String> taskIdReplace = new HashMap<String, String>();
        //        taskReplaces.put("TASK_ID", taskIdReplace);
        //        taskIdReplace.put("^1000$", "1");
        //        ts.add(new TableInput(dburl, username, password, "csa.t_task", taskSql, "task.new.sql", "TASK_ID",
        //                taskIdReset, null, outputFolder, null));
        //        ts.add(new TableInput(dburl, username, password, "csa.t_task_ext", taskExtSql, "task_ext.new.sql",
        //                "EXT_ID", taskExtIdReset, null, outputFolder, schedReplaces));
        return ts;
    }

    static String COPYRIGHT = "Generated with SqlGenTool v1.0 by realwronger";

    public static void main(String[] args) {
        SqlGenTool tool = new SqlGenTool();
        List<TableInput> inputList;
        //        inputList = tool.myTest();
        //        inputList = tool.csa();
        //        inputList = tool.paymentMessageEventCode();
        //        inputList = tool.pfs_payment();
        //        inputList = tool.pfs();
        //        inputList = tool.pfs_temp();
        //        inputList = tool.pfs_branch();
        //                inputList = tool.basis();
        //        inputList = tool.rms();
        inputList = tool.csa_new();
        for (TableInput t : inputList) {
            tool.genSingle(t);
        }
    }

    public void genSingle(TableInput t) {
        String querySql = composeQuerySql(t.tableName, t.selectSql);
        SqlRowSet rows = JdbcUtil.query(t.dburl, t.username, t.password, querySql);
        StringBuilder sb = new StringBuilder();
        addBlockComment(sb, COPYRIGHT);
        addLine(sb, 1);
        addRows(sb, rows, t);
        saveToFile(sb.toString(), t);
    }

    void saveToFile(String content, TableInput t) {
        try {
            if (StringUtils.isBlank(t.outputFileName)) {
                t.outputFileName = "DML_insert_" + t.tableName + ".sql";
            }
            String filePath = t.outputFolder + t.outputFileName;
            FileWriter fw = new FileWriter(filePath);
            fw.write(content);
            fw.close();
            TestUtil.sp("Saved file to " + filePath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    void addRows(StringBuilder sb, SqlRowSet rows, TableInput t) {
        SqlRowSetMetaData meta = rows.getMetaData();
        int columnCount = meta.getColumnCount();
        String template = createInsertTemplate(t.tableName, meta);
        while (rows.next()) {
            StringBuilder valsb = new StringBuilder();
            for (int i = 1; i <= columnCount; ++i) {
                String columnName = meta.getColumnName(i);
                if (t.resetNumColumn.equals(columnName)) {
                    valsb.append(t.resetNumInit++);
                } else if (t.columnsTosysdate.contains(columnName)) {
                    valsb.append("sysdate");
                } else {
                    filterValue(valsb, meta.getColumnTypeName(i), rows.getObject(i), t.columnReplace.get(columnName));
                }
                valsb.append(", ");
            }
            if (columnCount != 0) {
                valsb.setLength(valsb.length() - 2);
            }
            sb.append(String.format(template, valsb.toString()));
            addLine(sb, 2);
        }
    }

    void filterValue(StringBuilder valsb, String t, Object value, HashMap<String, String> replace) {
        String rst;
        if ("VARCHAR2".equals(t)) {
            rst = parseString((String) value, replace);
        } else if ("CHAR".equals(t)) {
            rst = parseString((String) value, replace);
        } else if ("CLOB".equals(t)) {
            SerialClob clob = (SerialClob) value;
            if (clob == null) {
                rst = NullValue;
            } else {
                try {
                    BufferedReader r = new BufferedReader(clob.getCharacterStream());
                    StringBuilder b = new StringBuilder();
                    String line;
                    while ((line = r.readLine()) != null) {
                        b.append(line);
                        b.append("\r\n");
                    }
                    rst = parseString(b.toString(), replace);
                } catch (Exception e) {
                    e.printStackTrace();
                    rst = "<SerialException>";
                }
            }
        } else if ("NUMBER".equals(t)) {
            rst = parseNumber(value, replace);
        } else if ("DATE".equals(t)) {
            rst = parseDate((Date) value);
        } else {
            throw new IllegalArgumentException(String.format("Type[%s] is not supported now!", t));
        }
        valsb.append(rst);
    }

    String parseNumber(Object n, HashMap<String, String> replace) {
        if (n == null) {
            return NullValue;
        }
        String ns = n.toString();
        if (replace != null) {
            for (String key : replace.keySet()) {
                ns = ns.replaceAll(key, replace.get(key));
            }
        }
        return ns;
    }

    String parseString(String s, HashMap<String, String> replace) {
        if (s == null) {
            return NullValue;
        }
        s = s.replaceAll("'", "''");
        //        s = s.replace("&", "&&");
        if (replace != null) {
            for (String key : replace.keySet()) {
                s = s.replaceAll(key, replace.get(key));
            }
        }
        return String.format("'%s'", s);
    }

    String parseDate(Date d) {
        if (d == null) {
            return NullValue;
        }
        return String.format("to_date('%s', 'yyyy-mm-dd hh24:mi:ss')",
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(d));
    }

    static String NullValue = "null";

    String parseNull(Object value) {
        if (value == null) {
            return NullValue;
        } else {
            return value.toString();
        }
    }

    String createInsertTemplate(String tablename, SqlRowSetMetaData meta) {
        StringBuilder tpl = new StringBuilder();
        tpl.append("insert into ").append(tablename).append(" (");
        String[] columns = meta.getColumnNames();
        for (String column : columns) {
            tpl.append(column).append(", ");
        }
        if (columns.length != 0) {
            tpl.setLength(tpl.length() - 2);
        }
        tpl.append(")\r\nvalues (%s);");
        return tpl.toString();
    }

    String composeQuerySql(String tableName, String selectSql) {
        if (StringUtils.isNotBlank(selectSql)) {
            return selectSql;
        }
        return "select * from " + tableName;
    }

    void addBlockComment(StringBuilder sb, String message) {
        sb.append("-----------------------------------------------------------------\r\n");
        sb.append(String.format("-- %-60s--\r\n", message));
        sb.append("-----------------------------------------------------------------\r\n");
        sb.append("\r\n");
        sb.append("set define off;\r\n");
    }

    void addLine(StringBuilder sb, int count) {
        while (--count >= 0) {
            sb.append("\r\n");
        }
    }
}
